Relation between RAM / shmmax / shmall / shared_buffers

--_d7e1a313-3681-4457-a1d5-94acec9694d2_
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable


Hi=2C
I am having a transactional database heavy on parallel reads and updates. A=
part from hardware optimization=2C I want to ensure that my system paramete=
rs are optimized as well. Following is what I am doing on my test system ha=
ving 4GB RAM. Please let me know if the logic sounds ok (at least at high-l=
evel) and if there are other related parameters I should tweak as well:
RAM =3D 4GB1. Keeping the kernel parameter kernel.shmmax at 75% of RAM (i.e=
.. at 3GB )kernel.shmmax =3D 32212254722. Keeping the kernel parameter shmal=
l at the same value. Because shmall is measured in number of pages and each=
page on my linux is 4096 bytes=2C having kernel.shmall =3D 786432 (786=
432 * 4096 =3D 3221225472=2C same as shmmax)3. Keeping the shared_buffer pa=
rameter in the postgresql.conf file to be 25% of the kernel.shmmax. i.e. sh=
ared_buffers =3D 768MB (25% of 3072 MB)

Thanks=2C-Bala
=20
____________________________________________________________ _____
The New Busy is not the old busy. Search=2C chat and e-mail from your inbox=
..
http://www.windowslive.com/campaign/thenewbusy?ocid=3DPID283 26::T:WLMTAGL:O=
N:WL:en-US:WM_HMP:042010_3=

--_d7e1a313-3681-4457-a1d5-94acec9694d2_
Content-Type: text/html; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Re: Relation between RAM / shmmax / shmall /

Balkrishna Sharma wrote:

> [Are there] other related parameters I should tweak as well[?]

There are several which should probably be adjusted. See:

http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Serve r

-Kevin

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: Relation between RAM / shmmax / shmall / shared_buffers

This is a multi-part message in MIME format.
--------------050204070503070109050300
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Balkrishna Sharma wrote:
> 1. Keeping the kernel parameter kernel.shmmax at 75% of RAM (i.e. at
> 3GB )kernel.shmmax = 3221225472
> 2. Keeping the kernel parameter shmall at the same value. Because
> shmall is measured in number of pages and each page on my linux is
> 4096 bytes, having kernel.shmall = 786432 (786432 * 4096 =
> 3221225472, same as shmmax)

There's little reason to put shmmax at over 50% of RAM, because
shared_buffers is going to be significantly lower than that even. I use
the attached script for this job now; I got sick of doing the math
manually all the time. If you're on a system that supports returning
memory info using getconf, it outputs the lines you need to put into the
kernel configuration.

--
Greg Smith 2ndQuadrant US Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com www.2ndQuadrant.us


--------------050204070503070109050300
Content-Type: text/plain;
name="shmsetup"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline;
filename="shmsetup"

#!/bin/bash

# Output lines suitable for sysctl configuration based
# on total amount of RAM on the system. The output
# will allow up to 50% of physical memory to be allocated
# into shared memory.

# On Linux, you can use it as follows (as root):
#
# ./shmsetup >> /etc/sysctl.conf
# sysctl -p

# Early FreeBSD versions do not support the sysconf interface
# used here. The exact version where this works hasn't
# been confirmed yet.

page_size=`getconf PAGE_SIZE`
phys_pages=`getconf _PHYS_PAGES`

if [ -z "$page_size" ]; then
echo Error: cannot determine page size
exit 1
fi

if [ -z "$phys_pages" ]; then
echo Error: cannot determine number of memory pages
exit 2
fi

shmall=`expr $phys_pages / 2`
shmmax=`expr $shmall \* $page_size`

echo \# Maximum shared segment size in bytes
echo kernel.shmmax = $shmmax
echo \# Maximum number of shared memory segments in pages
echo kernel.shmall = $shmall

--------------050204070503070109050300
Content-Type: text/plain
Content-Disposition: inline
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable


--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

--------------050204070503070109050300--